-- common..p_InsPayment 'TUTU','1507',67.0,34.0,1.0,4556.0,5345.0,5345.0,2922218.4,'CMV'
use common
go
IF OBJECT_ID('dbo.p_InsPayment') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_InsPayment
    IF OBJECT_ID('dbo.p_InsPayment') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_InsPayment >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_InsPayment >>>'
END
go
create proc p_InsPayment
(
	@InvoiceNumber          varchar(50)						,	   	-- InvoiceNumber
	@CustomerName          	varchar(50)						,	   	-- CustomerName
	@OpenAmount           	decimal(10,3)					,	   	-- OpenAmount
	@PaymentAmount          decimal(10,3)					,	   	-- PaymentAmount
	@CompanyName			varchar(50)								-- CompanyName  data belongs to which CMP

) as
begin
	-- declare
    declare @PaymentDate	datetime	-- datetime when this row was updated
    declare @UserUpdated	varchar(15)	-- user name that updated this row
	declare @Msg 		varchar(255)	-- last error message
	declare @ErrVal		int		-- last error number
	-- initialize
	select @ErrVal=0
	select @PaymentDate=getdate()
	select @UserUpdated=suser_name()
	begin tran p_InsPayment
		select @ErrVal=0
		if @ErrVal=0
		begin
				insert into common..t_Payment
				(InvoiceNumber,CustomerName,
				  OpenAmount,PaymentAmount,PaymentDate,CompanyName)
				values(@InvoiceNumber,@CustomerName,
				  @OpenAmount,@PaymentAmount,@PaymentDate,@CompanyName)
				select @ErrVal=@@error
		end
		if @ErrVal!=0
		begin
			rollback tran p_InsPayment
			select @ErrVal=99999,@Msg="Failed to insert new Payment:" + @InvoiceNumber
			goto ErrorHandler
		end
	commit tran p_InsPayment
    --end

	if @ErrVal!=0
	begin
			select @ErrVal=99999, @Msg="Failed to insert ItemCode:" + @InvoiceNumber
			goto ErrorHandler
	end
Done:

	return 0 

ErrorHandler:
	raiserror @ErrVal @Msg
end
go
EXEC sp_procxmode 'dbo.p_InsPayment','unchained'
go
IF OBJECT_ID('dbo.p_InsPayment') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_InsPayment >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_InsPayment >>>'
go
GRANT EXECUTE ON dbo.p_InsPayment TO public
go